﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 畜牧业审核程序国产.Models.ToolsClass;

namespace 畜牧业审核程序国产.Models.SimpleFactory
{
    internal class Swine
    {
        string withYearAndSeasonTableNameNow = Global.GetWithPlatformYearSeason().Item1 + "A209";
        string withYearAndSeasonTableNamePre = Global.GetWithPlatformYearSeason().Item2 + "A209";
        string withYearAndSeasonTableNameYOY = Global.GetWithPlatformYearSeason().Item3 + "A209";


        public DataTable GetDTAllIndicatorsOfSubordinateThisYear(KeyValuePair<string, string> selectedCity, KeyValuePair<string, string> selectedCountry
            , KeyValuePair<string, string> selectedTown, string[] tableNames, int 期别季)
        {
            try
            {
                MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
                getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
                string sqlToGetThisYear = null;
                if (selectedCity.Value.Equals("全部"))//全省各地市的
                {
                    sqlToGetThisYear =
                   $"  WITH 存栏合计 AS(SELECT 市, 市代码, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' " +
                        " GROUP BY 市,市代码), " +
                     " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         " FROM 一到本季度用表 " +
                         " GROUP BY 市,市代码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                     " ORDER BY  市代码; ";
                }
                else if (selectedCity.Value.Equals("全部") == false && selectedCountry.Value.Equals("全部") == true)//某个地市的
                {
                    sqlToGetThisYear = $"  WITH 存栏合计 AS(SELECT 县, 县代码, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                        " GROUP BY 县,县代码), " +
                     " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表  where 市代码 = '{selectedCity.Key}' " +
                         " GROUP BY 县,县代码 )" +
                      "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                     " ORDER BY  县代码; ";
                }
                else if (selectedCity.Value.Equals("全部") == false && selectedCountry.Value.Equals("全部") == false && selectedTown.Value.Equals("全部"))//某个具体县的
                {
                    sqlToGetThisYear =
                    $"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                        " GROUP BY 乡,乡镇代码), " +
                     " 出栏合计 AS(SELECT 乡,乡镇代码, SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where  县代码 = '{selectedCountry.Key}' " +
                         " GROUP BY 乡,乡镇代码 )" +
                      "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                     " ORDER BY  乡镇代码; ";
                }


                DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetThisYear);
                DataRow sumRow = dt.NewRow();
                sumRow[0] = "规模户合计";
                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    var sum = dt.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                    sumRow[i] = sum;
                }
                dt.Rows.Add(sumRow);
                return dt;
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},方法为{ex.TargetSite}");
                throw;
            }
        }
        public DataTable GetDTAllIndicatorsOfSubordinateThisSeason(KeyValuePair<string, string> selectedCity, KeyValuePair<string, string> selectedCountry
            , KeyValuePair<string, string> selectedTown)
        {
            try
            {
                string sqlToGetAllSumIndicators = null;
                if (selectedCity.Value.Equals("全部"))
                {
                    sqlToGetAllSumIndicators = $"select 市,市代码,SUM([当季出栏数]) as [规模户当季出栏数],SUM([期末存栏合计01]) as [规模户期末存栏合计01]," +
                        $"SUM([25公斤以下仔猪存栏数02]) as [规模户25公斤以下仔猪存栏数02],SUM([待育肥猪存栏数03]) as [规模户待育肥猪存栏数03]," +
                        $"SUM([50公斤以上待育肥猪存栏数04]) as [规模户50公斤以上待育肥猪存栏数04],SUM([种猪存栏数05]) as [规模户种猪存栏数05]," +
                        $"SUM([能繁殖母猪存栏数06]) as [规模户能繁殖母猪存栏数06],SUM([当季增加头数07]) as [规模户当季增加头数07]," +
                        $"SUM([自繁头数08]) as [规模户自繁头数08],SUM([购进头数09]) as [规模户购进头数09],SUM([当季减少头数10]) as [规模户当季减少头数10]," +
                        $"SUM([自宰肥猪头数11]) as [规模户自宰肥猪头数11],SUM([出售肥猪头数（不包括出售待育肥猪）12]) as [规模户出售肥猪头数（不包括出售待育肥猪）12]," +
                        $"SUM([出售肥猪金额（元）13]) as [规模户出售肥猪金额（元）13],SUM([出售肥猪重量（公斤）14]) as [规模户出售肥猪重量（公斤）14]," +
                        $"SUM([其它原因减少（包括死亡、疫病捕杀）15]) as [规模户其它原因减少（包括死亡、疫病捕杀）15]," +
                        $"SUM([出售25公斤以下仔猪头数16]) as [规模户出售25公斤以下仔猪头数16]," +
                        $"SUM([出售25公斤以下仔猪金额（元）17]) as [规模户出售25公斤以下仔猪金额（元）17]," +
                        $"SUM([出售25公斤以下仔猪重量（公斤）18]) as [规模户出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} group by 市,市代码";
                }
                else if (selectedCity.Value.Equals("全部") == false && selectedCountry.Value.Equals("全部") == true) //某个具体市的
                {
                    sqlToGetAllSumIndicators = $"select 县,县代码,SUM([当季出栏数]) as [规模户当季出栏数],SUM([期末存栏合计01]) as [规模户期末存栏合计01]," +
                        $"SUM([25公斤以下仔猪存栏数02]) as [规模户25公斤以下仔猪存栏数02],SUM([待育肥猪存栏数03]) as [规模户待育肥猪存栏数03]," +
                        $"SUM([50公斤以上待育肥猪存栏数04]) as [规模户50公斤以上待育肥猪存栏数04],SUM([种猪存栏数05]) as [规模户种猪存栏数05]," +
                        $"SUM([能繁殖母猪存栏数06]) as [规模户能繁殖母猪存栏数06],SUM([当季增加头数07]) as [规模户当季增加头数07]," +
                        $"SUM([自繁头数08]) as [规模户自繁头数08],SUM([购进头数09]) as [规模户购进头数09],SUM([当季减少头数10]) as [规模户当季减少头数10]," +
                        $"SUM([自宰肥猪头数11]) as [规模户自宰肥猪头数11],SUM([出售肥猪头数（不包括出售待育肥猪）12]) as [规模户出售肥猪头数（不包括出售待育肥猪）12]," +
                        $"SUM([出售肥猪金额（元）13]) as [规模户出售肥猪金额（元）13],SUM([出售肥猪重量（公斤）14]) as [规模户出售肥猪重量（公斤）14]," +
                        $"SUM([其它原因减少（包括死亡、疫病捕杀）15]) as [规模户其它原因减少（包括死亡、疫病捕杀）15]," +
                        $"SUM([出售25公斤以下仔猪头数16]) as [规模户出售25公斤以下仔猪头数16]," +
                        $"SUM([出售25公斤以下仔猪金额（元）17]) as [规模户出售25公斤以下仔猪金额（元）17]," +
                        $"SUM([出售25公斤以下仔猪重量（公斤）18]) as [规模户出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} where 市代码 = '{selectedCity.Key}' group by 县,县代码 order by 县代码";
                }
                else if (selectedCountry.Value.Equals("全部") == false && selectedTown.Value.Equals("全部") == true)//某个具体县的
                {
                    sqlToGetAllSumIndicators = $"select 乡,乡镇代码,SUM([当季出栏数]) as [规模户当季出栏数],SUM([期末存栏合计01]) as [规模户期末存栏合计01]," +
                        $"SUM([25公斤以下仔猪存栏数02]) as [规模户25公斤以下仔猪存栏数02],SUM([待育肥猪存栏数03]) as [规模户待育肥猪存栏数03]," +
                        $"SUM([50公斤以上待育肥猪存栏数04]) as [规模户50公斤以上待育肥猪存栏数04],SUM([种猪存栏数05]) as [规模户种猪存栏数05]," +
                        $"SUM([能繁殖母猪存栏数06]) as [规模户能繁殖母猪存栏数06],SUM([当季增加头数07]) as [规模户当季增加头数07]," +
                        $"SUM([自繁头数08]) as [规模户自繁头数08],SUM([购进头数09]) as [规模户购进头数09],SUM([当季减少头数10]) as [规模户当季减少头数10]," +
                        $"SUM([自宰肥猪头数11]) as [规模户自宰肥猪头数11],SUM([出售肥猪头数（不包括出售待育肥猪）12]) as [规模户出售肥猪头数（不包括出售待育肥猪）12]," +
                        $"SUM([出售肥猪金额（元）13]) as [规模户出售肥猪金额（元）13],SUM([出售肥猪重量（公斤）14]) as [规模户出售肥猪重量（公斤）14]," +
                        $"SUM([其它原因减少（包括死亡、疫病捕杀）15]) as [规模户其它原因减少（包括死亡、疫病捕杀）15]," +
                        $"SUM([出售25公斤以下仔猪头数16]) as [规模户出售25公斤以下仔猪头数16]," +
                        $"SUM([出售25公斤以下仔猪金额（元）17]) as [规模户出售25公斤以下仔猪金额（元）17]," +
                        $"SUM([出售25公斤以下仔猪重量（公斤）18]) as [规模户出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} where 县代码 = '{selectedCountry.Key}' group by 乡,乡镇代码 order by 乡镇代码";

                }


                MyDBOperator myoperator = MyDBOperator.DBOperatorInstance;
                DataTable dt = myoperator.GetDataTable(sqlToGetAllSumIndicators);
                DataRow sumRow = dt.NewRow();
                sumRow[0] = "规模户合计";
                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    var sum = dt.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                    sumRow[i] = sum;
                }
                dt.Rows.Add(sumRow);
                return dt;
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},方法为{ex.TargetSite}");
                throw;
            }


        }
        public DataTable GetDTAllIndicatorsOfAllOwnersLocalThisYear(KeyValuePair<string, string> selectedCity, KeyValuePair<string, string> selectedCountry
           , KeyValuePair<string, string> selectedTown, string[] tableNames, int 期别季)
        {
            try
            {
                MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
                getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(tableNames);
                string sqlToGetAllOwnersLocalAllIndicators = null;
                if (selectedCity.Value.Equals("全部"))//全省的
                {
                    sqlToGetAllOwnersLocalAllIndicators = $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}' " +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         " FROM 一到本季度用表 " +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
                }
                else if (selectedCountry.Value.Equals("全部"))//某个市分县展示的
                {
                    sqlToGetAllOwnersLocalAllIndicators = $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}'  and 市代码 ='{selectedCity.Key}' " +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where 市代码 = '{selectedCity.Key}'" +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
                }
                else if (selectedTown.Value.Equals("全部"))//某个县分乡镇展示的
                {
                    sqlToGetAllOwnersLocalAllIndicators =
                       $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}'  and 县代码 ='{selectedCountry.Key}' " +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where 县代码 = '{selectedCountry.Key}'" +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
                }

                else if (selectedTown.Value.Equals("全部") == false)//某个具体乡镇的
                {
                    sqlToGetAllOwnersLocalAllIndicators =
                       $"  WITH 存栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型, SUM(期末存栏合计01) AS 期末合计数01, SUM([25公斤以下仔猪存栏数02]) as [25公斤以下仔猪存栏数02], SUM([待育肥猪存栏数03]) as 待育肥猪存栏数03, " +
                       "  SUM([50公斤以上待育肥猪存栏数04]) as [50公斤以上待育肥猪存栏数04], SUM(种猪存栏数05) AS 种猪存栏数05, SUM(能繁殖母猪存栏数06) as 能繁殖母猪存栏数06 " +
                         $"FROM 一到本季度用表  WHERE 期别季 = '{期别季}'  and 乡镇代码 ='{selectedTown.Key}'" +
                        " GROUP BY 户编码), " +
                     " 出栏合计 AS(SELECT 市,市代码,县,县代码,乡,乡镇代码,户编码, 户主姓名,养殖户单位类型,SUM(当季出栏数) AS 当季出栏数, SUM(当季增加头数07) as 当季增加头数07,  sum(自繁头数08) as 自繁头数08, " +
                       "   sum(购进头数09) as 购进头数09,  sum(当季减少头数10) as 当季减少头数10,  sum(自宰肥猪头数11) as 自宰肥猪头数11, " +
                          "sum([出售肥猪头数（不包括出售待育肥猪）12]) as [出售肥猪头数（不包括出售待育肥猪）12], sum([出售肥猪金额（元）13]) as [出售肥猪金额（元）13], " +
                         " sum([出售肥猪重量（公斤）14]) as [出售肥猪重量（公斤）14],   sum([其它原因减少（包括死亡、疫病捕杀）15]) as [其它原因减少（包括死亡、疫病捕杀）15], " +
                         " sum(出售25公斤以下仔猪头数16) as 出售25公斤以下仔猪头数16,   sum([出售25公斤以下仔猪金额（元）17]) as [出售25公斤以下仔猪金额（元）17], " +
                         " sum([出售25公斤以下仔猪重量（公斤）18]) as [出售25公斤以下仔猪重量（公斤）18] " +
                         $" FROM 一到本季度用表 where 乡镇代码 = '{selectedTown.Key}'" +
                         " GROUP BY 户编码 )" +
                      "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, COALESCE(c.县, o.县) AS 县,COALESCE(c.县代码, o.县代码) AS 县代码," +
                      "COALESCE(c.乡, o.乡) AS 乡,COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码,COALESCE(c.户编码, o.户编码) AS 户编码,COALESCE(c.户主姓名, o.户主姓名) AS 户主姓名,COALESCE(c.养殖户单位类型, o.养殖户单位类型) AS 养殖户单位类型," +
                      "COALESCE(o.当季出栏数, 0) AS 当季出栏数, " +
                         " COALESCE(c.期末合计数01, 0) AS 期末合计数01, COALESCE(c.[25公斤以下仔猪存栏数02], 0) as [25公斤以下仔猪存栏数02], " +
                         " COALESCE(c.[待育肥猪存栏数03], 0) as [待育肥猪存栏数03],COALESCE(c.[50公斤以上待育肥猪存栏数04], 0) as [50公斤以上待育肥猪存栏数04], " +
                         " COALESCE(c.[种猪存栏数05], 0) as [种猪存栏数05],  COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06],   COALESCE(o.当季增加头数07, 0) AS 当季增加头数07, " +
                         " COALESCE(o.自繁头数08, 0) AS 自繁头数08, COALESCE(o.购进头数09, 0) AS 购进头数09, COALESCE(o.当季减少头数10, 0) AS 当季减少头数10, " +
                         " COALESCE(o.自宰肥猪头数11, 0) AS 自宰肥猪头数11, COALESCE(o.[出售肥猪头数（不包括出售待育肥猪）12], 0) AS[出售肥猪头数（不包括出售待育肥猪）12], " +
                          "COALESCE(o.[出售肥猪金额（元）13], 0) AS[出售肥猪金额（元）13],COALESCE(o.[出售肥猪重量（公斤）14], 0) AS[出售肥猪重量（公斤）14], " +
                         " COALESCE(o.[其它原因减少（包括死亡、疫病捕杀）15], 0) AS[其它原因减少（包括死亡、疫病捕杀）15],COALESCE(o.出售25公斤以下仔猪头数16, 0) AS 出售25公斤以下仔猪头数16, " +
                         " COALESCE(o.[出售25公斤以下仔猪金额（元）17], 0) AS[出售25公斤以下仔猪金额（元）17],  COALESCE(o.[出售25公斤以下仔猪重量（公斤）18], 0) AS[出售25公斤以下仔猪重量（公斤）18] " +
                     " FROM " +
                         " 存栏合计 c " +
                     " FULL OUTER JOIN " +
                         " 出栏合计 o ON c.户编码 = o.户编码 " +
                     " ORDER BY  户编码; ";
                }

                DataTable dt = getSimpleAddOperator.GetDataTable(sqlToGetAllOwnersLocalAllIndicators);
                DataRow sumRow = dt.NewRow();
                sumRow[0] = "规模户合计";
                for (int i = 9; i < dt.Columns.Count; i++)
                {
                    var sum = dt.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                    sumRow[i] = sum;
                }
                dt.Rows.Add(sumRow);
                return dt;
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},方法为{ex.TargetSite}");
                throw;
            }
        }
        public DataTable GetDTAllIndicatorsOfAllOwnersLocalThisSeason(KeyValuePair<string, string> selectedCity, KeyValuePair<string, string> selectedCountry
            , KeyValuePair<string, string> selectedTown)
        {
            try
            {
                string sqlToGetAllIndicators = null;
                if (selectedCity.Value.Equals("全部"))//全省的
                {
                    sqlToGetAllIndicators = $"select 市, 县,乡,户主姓名,养殖户单位类型,[当季出栏数],[期末存栏合计01]" +
                        $",[25公斤以下仔猪存栏数02],[待育肥猪存栏数03],[50公斤以上待育肥猪存栏数04],[种猪存栏数05]" +
                        $",[能繁殖母猪存栏数06],[当季增加头数07],[自繁头数08],[购进头数09],[当季减少头数10],[自宰肥猪头数11]" +
                        $",[出售肥猪头数（不包括出售待育肥猪）12],[出售肥猪金额（元）13],[出售肥猪重量（公斤）14]," +
                        $"[其它原因减少（包括死亡、疫病捕杀）15],[出售25公斤以下仔猪头数16],[出售25公斤以下仔猪金额（元）17]" +
                        $",[出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} ";
                    ;
                }
                else if (selectedCountry.Value.Equals("全部"))//某个市分县展示的
                {
                    sqlToGetAllIndicators = $"select 市, 县,乡,户主姓名,养殖户单位类型,[当季出栏数],[期末存栏合计01]" +
                        $",[25公斤以下仔猪存栏数02],[待育肥猪存栏数03],[50公斤以上待育肥猪存栏数04],[种猪存栏数05]" +
                        $",[能繁殖母猪存栏数06],[当季增加头数07],[自繁头数08],[购进头数09],[当季减少头数10],[自宰肥猪头数11]" +
                        $",[出售肥猪头数（不包括出售待育肥猪）12],[出售肥猪金额（元）13],[出售肥猪重量（公斤）14]," +
                        $"[其它原因减少（包括死亡、疫病捕杀）15],[出售25公斤以下仔猪头数16],[出售25公斤以下仔猪金额（元）17]" +
                        $",[出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} where 市代码 = '{selectedCity.Key}'";
                    ;
                }
                else if (selectedTown.Value.Equals("全部"))//某个县分乡镇展示的
                {
                    sqlToGetAllIndicators = $"select 市, 县,乡,户主姓名,养殖户单位类型,[当季出栏数],[期末存栏合计01]" +
                        $",[25公斤以下仔猪存栏数02],[待育肥猪存栏数03],[50公斤以上待育肥猪存栏数04],[种猪存栏数05]" +
                        $",[能繁殖母猪存栏数06],[当季增加头数07],[自繁头数08],[购进头数09],[当季减少头数10],[自宰肥猪头数11]" +
                        $",[出售肥猪头数（不包括出售待育肥猪）12],[出售肥猪金额（元）13],[出售肥猪重量（公斤）14]," +
                        $"[其它原因减少（包括死亡、疫病捕杀）15],[出售25公斤以下仔猪头数16],[出售25公斤以下仔猪金额（元）17]" +
                        $",[出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} where 县代码 = '{selectedCountry.Key}'";
                }

                else if (selectedTown.Value.Equals("全部") == false)
                {
                    sqlToGetAllIndicators = $"select 市, 县,乡,户主姓名,养殖户单位类型,[当季出栏数],[期末存栏合计01]" +
                        $",[25公斤以下仔猪存栏数02],[待育肥猪存栏数03],[50公斤以上待育肥猪存栏数04],[种猪存栏数05]" +
                        $",[能繁殖母猪存栏数06],[当季增加头数07],[自繁头数08],[购进头数09],[当季减少头数10],[自宰肥猪头数11]" +
                        $",[出售肥猪头数（不包括出售待育肥猪）12],[出售肥猪金额（元）13],[出售肥猪重量（公斤）14]," +
                        $"[其它原因减少（包括死亡、疫病捕杀）15],[出售25公斤以下仔猪头数16],[出售25公斤以下仔猪金额（元）17]" +
                        $",[出售25公斤以下仔猪重量（公斤）18] from {withYearAndSeasonTableNameNow} where 乡镇代码 = '{selectedTown.Key}'";

                }
                MyDBOperator myDBOperator = MyDBOperator.DBOperatorInstance;
                DataTable dt = myDBOperator.GetDataTable(sqlToGetAllIndicators);
                DataRow sumRow = dt.NewRow();
                sumRow[0] = "规模户合计";
                for (int i = 5; i < dt.Columns.Count; i++)
                {
                    sumRow[i] = dt.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));

                }
                dt.Rows.Add(sumRow);
                return dt;
            }

            catch (Exception ex) {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},方法为{ex.TargetSite}"); 
                throw; }
        }
        public DataTable GetDTSingleIndicatorOfSubordinateThisSeason(KeyValuePair<string, string> selectedCity, KeyValuePair<string, string> selectedCountry
             , KeyValuePair<string, string> selectedTown, KeyValuePair<string, string> Indicator)
        {
            try
            {
                MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
                string sqlToGetAllIndicators = null;
                if (selectedCity.Value.Equals("全部"))//全省的
                {

                    //先复制三个表
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCityList", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });

                    DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 统计区划名称 as 市,乡镇区划代码 as 市代码  from 全省区划代码 where  LENGTH(乡镇区划代码) < 5");
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCityList");



                    //建立第一次左连接
                    //即建立本季度全市所有县的本季度数据的数据表
                    string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
                    string sqlToInsert = $"INSERT INTO  No1LeftJoin  (市,市代码,本季度{Indicator.Value})    select t1.市 as 市,t1.市代码 as 市代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCityList as t1 left join " +
                        $"(select 市代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
                    //string sqlforFirstLeftJoin = $"create table FirstLeftJoin  as ( select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
                    //    $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码))";
                    //dt1 = adoForShowSingleMsg.GetDataTable(sqlforFirstLeftJoin);
                    //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "No1LeftJoin");
                    //第二次左连接
                    //
                    string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
                    sqlToInsert = $"INSERT INTO No2LeftJoin (市,市代码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                        $"(select 市代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
                    //string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
                    //    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                    //第三次左连接 
                    string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
                    string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(市,市代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.市 as 市,t1.市代码 as 市代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                        $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                        $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                        $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                        $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                        $"   from ((select * from No2LeftJoin) as t1 left join (select 市代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                    //先把环比增幅和同比增幅的数据类型转成double,在sqlite 中不用转了
                    //string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                    //sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                    DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                    double SumOfIndicatorNow = (double)rowsum[0];
                    double SumOfIndicatorPre = (double)rowsum[1];
                    double SumOfIndicatorYOY = (double)rowsum[2];
                    //计算占比
                    List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                    if (SumOfIndicatorNow > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                    }
                    if (SumOfIndicatorPre > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                    }
                    if (SumOfIndicatorYOY > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                    }

                    foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                    {
                        adoForShowSingleMsg.ExecuteBySql(item);
                    }
                    DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                    DataRow SumRow = dt.NewRow();
                    SumRow[0] = SumRow[1] = "合计";

                    for (int i = 2; i < dt.Columns.Count; i++)
                    {
                        SumRow[i] = dt.AsEnumerable().Sum(row =>
                            Convert.ToDouble(row.Field<object>(i) ?? 0));
                        //把饼图需要的数据(就一个value)返回去

                    }
                    SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                    SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                    dt.Rows.Add(SumRow);


                    return dt;

                }
                else if (selectedCountry.Value.Equals("全部"))//某个市分县展示的
                {
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });

                    DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 统计区划名称 as 县,乡镇区划代码 as 县代码  from 全省区划代码 where 乡镇区划代码 like '{selectedCity.Key}%' and LENGTH(乡镇区划代码) < 9");
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCountryListOfThisCity");



                    //建立第一次左连接
                    //即建立本季度全市所有县的本季度数据的数据表
                    string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
                    string sqlToInsert = $"INSERT INTO  No1LeftJoin  (县,县代码,本季度{Indicator.Value})    select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
                        $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
                    //string sqlforFirstLeftJoin = $"create table FirstLeftJoin  as ( select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
                    //    $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码))";
                    //dt1 = adoForShowSingleMsg.GetDataTable(sqlforFirstLeftJoin);
                    //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "No1LeftJoin");
                    //第二次左连接
                    //
                    string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
                    sqlToInsert = $"INSERT INTO No2LeftJoin (县,县代码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                        $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
                    //string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
                    //    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                    //第三次左连接 
                    string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
                    string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(县,县代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.县 as 县,t1.县代码 as 县代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                        $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                        $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                        $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                        $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                        $"   from ((select * from No2LeftJoin) as t1 left join (select 县代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                    //先把环比增幅和同比增幅的数据类型转成double,在sqlite 中不用转了
                    //string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                    //sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                    DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                    double SumOfIndicatorNow = (double)rowsum[0];
                    double SumOfIndicatorPre = (double)rowsum[1];
                    double SumOfIndicatorYOY = (double)rowsum[2];
                    //计算占比
                    List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                    if (SumOfIndicatorNow > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                    }
                    if (SumOfIndicatorPre > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                    }
                    if (SumOfIndicatorYOY > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                    }

                    foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                    {
                        adoForShowSingleMsg.ExecuteBySql(item);
                    }

                    DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                    DataRow SumRow = dt.NewRow();
                    SumRow[0] = SumRow[1] = "合计";

                    for (int i = 2; i < dt.Columns.Count; i++)
                    {
                        SumRow[i] = dt.AsEnumerable().Sum(row =>
                            Convert.ToDouble(row.Field<object>(i) ?? 0));
                        //把饼图需要的数据(就一个value)返回去

                    }
                    SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                    SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                    dt.Rows.Add(SumRow);


                    return dt;
                }
                else if (selectedTown.Value.Equals("全部"))//某个县分乡镇展示的
                {
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllTownListOfThisCountry", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });

                    DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 统计区划名称 as 乡,乡镇区划代码 as 乡镇代码  from 全省区划代码 where 乡镇区划代码 like '{selectedCountry.Key}%' and LENGTH(乡镇区划代码) > 6");
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllTownListOfThisCountry");



                    //建立第一次左连接
                    //即建立本季度全市所有县的本季度数据的数据表
                    string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
                    string sqlToInsert = $"INSERT INTO  No1LeftJoin  (乡,乡镇代码,本季度{Indicator.Value})    select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllTownListOfThisCountry as t1 left join " +
                        $"(select 乡镇代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
                    //string sqlforFirstLeftJoin = $"create table FirstLeftJoin  as ( select t1.县 as 县,t1.县代码 as 县代码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllCountryListOfThisCity as t1 left join " +
                    //    $"(select 县代码,cast( sum([{Indicator.Value}]) as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码))";
                    //dt1 = adoForShowSingleMsg.GetDataTable(sqlforFirstLeftJoin);
                    //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "No1LeftJoin");
                    //第二次左连接
                    //
                    string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
                    sqlToInsert = $"INSERT INTO No2LeftJoin (乡,乡镇代码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                        $"(select 乡镇代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);
                    //string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
                    //    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                    //第三次左连接 
                    string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
                    string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(乡,乡镇代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                        $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                        $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                        $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                        $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                        $"   from ((select * from No2LeftJoin) as t1 left join (select 乡镇代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                    //先把环比增幅和同比增幅的数据类型转成double,在sqlite 中不用转了
                    //string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                    //sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                    DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                    double SumOfIndicatorNow = (double)rowsum[0];
                    double SumOfIndicatorPre = (double)rowsum[1];
                    double SumOfIndicatorYOY = (double)rowsum[2];
                    //计算占比
                    List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                    if (SumOfIndicatorNow > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                    }
                    if (SumOfIndicatorPre > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                    }
                    if (SumOfIndicatorYOY > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                    }

                    foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                    {
                        adoForShowSingleMsg.ExecuteBySql(item);
                    }

                    DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                    DataRow SumRow = dt.NewRow();
                    SumRow[0] = SumRow[1] = "合计";

                    for (int i = 2; i < dt.Columns.Count; i++)
                    {
                        SumRow[i] = dt.AsEnumerable().Sum(row =>
                            Convert.ToDouble(row.Field<object>(i) ?? 0));
                        //把饼图需要的数据(就一个value)返回去

                    }
                    SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                    SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                    dt.Rows.Add(SumRow);


                    return dt;
                }
                else if (selectedTown.Value.Equals("全部") == false)
                {
                    //先复制三个表
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllFarmerListOfThisTown", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
                    adoForShowSingleMsg.MergeTablesFromDatabaseToAMergeTableInDB(new string[] { withYearAndSeasonTableNameNow, withYearAndSeasonTableNamePre, withYearAndSeasonTableNameYOY }, "三个季度表简单合并的表");
                    //DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select 户主姓名,养殖户单位类型,户编码  from 三个季度表简单合并的表 where 乡镇代码 ='{selectdTown.Key}'");
                    //adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllFarmerListOfThisTown");

                    ////建立第一次左连接
                    ////即建立本季度全市所有县的本季度数据的数据表
                    //string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,户编码 TEXT,本季度{Indicator.Value} REAL);";
                    //adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
                    //string sqlToInsert = $"INSERT INTO  No1LeftJoin  (户主姓名,养殖户单位类型,户编码,本季度{Indicator.Value})    select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.户编码 as 户编码, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllFarmerListOfThisTown as t1 left join " +
                    //    $"(select 户主姓名,养殖户单位类型,户编码,cast( [{Indicator.Value}] as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} ) as t2 on t1.户编码 = t2.户编码)";
                    //adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

                    ////第二次左连接
                    ////
                    //string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,户编码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    //adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
                    //sqlToInsert = $"INSERT INTO No2LeftJoin (户主姓名,养殖户单位类型,户编码,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.户编码 as 户编码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                    //    $"(select 户主姓名,养殖户单位类型,户编码, [{Indicator.Value}] as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} ) as t2 on t1.户编码 = t2.户编码)";
                    //adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

                    ////第三次左连接 
                    //string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,户编码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
                    //adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
                    //string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(户主姓名,养殖户单位类型,户编码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.户编码 as 户编码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                    //    $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                    //    $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                    //    $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                    //    $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                    //    $"   from ((select * from No2LeftJoin) as t1 left join (select 户主姓名,养殖户单位类型,户编码,[{Indicator.Value}] as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} ) as t2 on t1.户编码 = t2.户编码) ";
                    //adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);


                    DataTable dt1 = adoForShowSingleMsg.GetDataTable($"select DISTINCT 户主姓名,养殖户单位类型  from 三个季度表简单合并的表 where 乡镇代码 ='{selectedTown.Key}'");
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllFarmerListOfThisTown");



                    //建立第一次左连接
                    //即建立本季度全市所有县的本季度数据的数据表
                    string sqlToCreateFirstLeftJoin = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirstLeftJoin);
                    string sqlToInsert = $"INSERT INTO  No1LeftJoin  (户主姓名,养殖户单位类型,本季度{Indicator.Value})    select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型, cast( t2.[本季度{Indicator.Value}] as INTEGER) as [本季度{Indicator.Value}]  from (TableAllFarmerListOfThisTown as t1 left join " +
                        $"(select 户主姓名,养殖户单位类型,cast( [{Indicator.Value}] as INTEGER ) as [本季度{Indicator.Value}] from {withYearAndSeasonTableNameNow} ) as t2 on t1.户主姓名= t2.户主姓名)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

                    //第二次左连接
                    //
                    string sqlToCreateSecondLeftJoin = $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (户主姓名 TEXT,养殖户单位类型 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecondLeftJoin);
                    sqlToInsert = $"INSERT INTO No2LeftJoin (户主姓名,养殖户单位类型,本季度{Indicator.Value},上季度{Indicator.Value}) select t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                        $"(select 户主姓名,养殖户单位类型, [{Indicator.Value}] as [上季度{Indicator.Value}] from {withYearAndSeasonTableNamePre} ) as t2 on t1.户主姓名= t2.户主姓名)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsert);

                    //第三次左连接 
                    string sqlToCreateThirdLeftJoin = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (户主姓名 TEXT,养殖户单位类型  TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅（%）] REAL,[同比增幅（%）] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThirdLeftJoin);
                    string sqlForThirdLeftJoin = $"INSERT INTO No3LeftJoin(户主姓名,养殖户单位类型,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅（%）],[同比增幅（%）]) SELECT  t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                        $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                        $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                        $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅（%）] , " +
                        $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅（%）]  " +
                        $"   from ((select * from No2LeftJoin) as t1 left join (select 户主姓名,养殖户单位类型,[{Indicator.Value}] as [同比季度{Indicator.Value}] from {withYearAndSeasonTableNameYOY} ) as t2 on t1.户主姓名= t2.户主姓名) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);

                    DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                    double SumOfIndicatorNow = Convert.ToDouble(rowsum.IsNull(0) ? 0 : rowsum[0]);
                    double SumOfIndicatorPre = Convert.ToDouble(rowsum.IsNull(1) ? 0 : rowsum[1]);
                    double SumOfIndicatorYOY = Convert.ToDouble(rowsum.IsNull(2) ? 0 : rowsum[2]);
                    //计算占比
                    List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                    if (SumOfIndicatorNow > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                    }
                    if (SumOfIndicatorPre > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                    }
                    if (SumOfIndicatorYOY > 0)
                    {
                        ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                    }

                    foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                    {
                        adoForShowSingleMsg.ExecuteBySql(item);
                    }

                    DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                    DataRow SumRow = dt.NewRow();
                    SumRow[0] = SumRow[1] = "合计";

                    for (int i = 2; i < dt.Columns.Count; i++)
                    {
                        SumRow[i] = dt.AsEnumerable().Sum(row =>
                            Convert.ToDouble(row.Field<object>(i) ?? 0));
                        //把饼图需要的数据(就一个value)返回去

                    }
                    SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                    SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                    dt.Rows.Add(SumRow);


                    return dt;
                }
                else
                {
                    return null;
                }

            }
            catch (Exception ex) {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},方法为{ex.TargetSite}"); 
                throw; }
        }
    }
}
